﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using Class_Entity;
using System.Web.Configuration;
namespace DataAccess
{
    public class DAUsers
    {
        SqlConnection con;
        SqlCommand cmd;

        public DataSet Login_Accounts(Users us)
        {
            DataSet ds = new DataSet();
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();
                cmd = new SqlCommand("spLogin", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                cmd.Parameters.AddWithValue("@password", us.Password);
                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return ds;
        }

        public bool Add_Users(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spAddUser",con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                cmd.Parameters.AddWithValue("@password", us.Password);
                cmd.Parameters.AddWithValue("@fullname", us.Fullname);
                cmd.Parameters.AddWithValue("@email", us.Email);
                cmd.Parameters.AddWithValue("@address", us.Address);
                cmd.Parameters.AddWithValue("@tel", us.Tel);

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Add_Admins(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spAddAdmin", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                cmd.Parameters.AddWithValue("@password", us.Password);
                cmd.Parameters.AddWithValue("@email", us.Email);
                cmd.Parameters.AddWithValue("@RoleCode", us.RoleCode);

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Add_CVurl(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spAddCVurl",con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                cmd.Parameters.AddWithValue("@CV", us.CVURL);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Edit_Users(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spEditUsersInfo",con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                cmd.Parameters.AddWithValue("@password", us.Password);
                cmd.Parameters.AddWithValue("@fullname",us.Fullname);
                cmd.Parameters.AddWithValue("@email", us.Email);
                cmd.Parameters.AddWithValue("@address", us.Address);
                cmd.Parameters.AddWithValue("@tel", us.Tel);
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally{
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Edit_UserPassword(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spEditPasswordUser",con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@id", us.ID);
                cmd.Parameters.AddWithValue("@password", us.Password);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Edit_UserRoleCode(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spEditRole", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", us.ID);
                cmd.Parameters.AddWithValue("@RoleCode", us.RoleCode);
                cmd.ExecuteNonQuery();
                con.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return true;
        }

        public bool Delete_User(Users us)
        {
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spDeleteUser", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@ID",us.ID);

                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }

            return true;
        }

        public DataSet Find_UserByUsername(Users us)
        {
            DataSet ds = new DataSet();
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spFindUserByUsername", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username",us.Username);
                
                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }

            return ds;
        }

        public DataSet Find_UserByFullname(Users us)
        {
            DataSet ds = new DataSet();
            try
            {
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spFindUserByFullname", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@fullname", us.Fullname);
                
                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }

            return ds;
        }

        public DataSet Load_Users(Users us)
        {
            DataSet ds = new DataSet();
            try
            {
                
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spLoadUsers", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return ds;
        }

        public DataSet Load_Roles(Users us)
        {
            DataSet ds = new DataSet();
            try
            {

                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spLoadRoles", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return ds;
        }

        public DataSet Load_AdminAccounts(Users us)
        {
            DataSet ds = new DataSet();
            try
            {

                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spLoadAdminAccounts", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return ds;
        }

        public DataSet Load_UserDetail(Users us)
        {
            DataSet ds = new DataSet();
            try
            {
                
                con = ConnectionToDataBase.StartConnection();
                con.Open();

                cmd = new SqlCommand("spLoadDetailUsers", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter data = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@username", us.Username);
                
                cmd.ExecuteNonQuery();
                data.Fill(ds);
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionToDataBase.CloseConnection();
            }
            return ds;
        }
    }
}
